﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using Microsoft.Office.Interop.Excel;

namespace RESP_IIWaterAssessment
{
    public partial class AVPHarvestFormReport : Form
    {
        public AVPHarvestFormReport()
        {
            InitializeComponent();
        }

        int regID, avpID, cultID, year;



        private void AVPHarvestFormReport_Load(object sender, EventArgs e)
        {
            cbRegion.SelectedIndexChanged -= cbRegion_SelectedIndexChanged;
            cbRegion.DataSource = UtilClass.FillCBRegions();
            cbRegion.DisplayMember = "Name";
            cbRegion.ValueMember = "ID";

            if (InformationClass.isAdmin)
            {
                cbRegion.SelectedIndex = -1;
                cbRegion.Text = string.Empty;
                cbRegion.SelectedIndexChanged += new EventHandler(cbRegion_SelectedIndexChanged);
            }
            else
            {
                chbRegion.Checked = true;
                chbRegion.Enabled = false;
                cbRegion.Enabled = false;
                regID = InformationClass.regionID;
                cbRegion.SelectedIndexChanged += new EventHandler(cbRegion_SelectedIndexChanged);
                cbRegion.SelectedValue = regID;
                cbRegion_SelectedIndexChanged(cbRegion, null);
                cbAVP.Enabled = true;
                cbAVP.Text = string.Empty;
                //toolStrip1.Visible = false;
            }

            for (int i = 1991; i < 2100; i++)
            {
                cbYear.Items.Add(i);
            }
            cbYear.SelectedIndex = cbYear.FindStringExact(DateTime.Now.Year.ToString());

            cbCulture.SelectedIndexChanged -= cbCulture_SelectedIndexChanged;
            cbCulture.DataSource = UtilClass.GetCBCultureDataSourse();
            cbCulture.DisplayMember = "Name";
            cbCulture.ValueMember = "ID";
            cbCulture.SelectedIndex = -1;
            cbCulture.SelectedIndexChanged += new EventHandler(cbCulture_SelectedIndexChanged);
        }

        private void cbRegion_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cbRegion.SelectedIndex >= 0)
            {
                cbAVP.Enabled = true;
                regID = Convert.ToInt32(cbRegion.SelectedValue);
                cbAVP.SelectedIndexChanged -= cbAVP_SelectedIndexChanged;
                cbAVP.DataSource = UtilClass.FillAVP(regID);
                cbAVP.ValueMember = "ID";
                cbAVP.DisplayMember = "Name";
                cbAVP.SelectedIndex = -1;
                cbAVP.Text = string.Empty;
                cbAVP.SelectedIndexChanged += new EventHandler(cbAVP_SelectedIndexChanged);
            }
        }

        private void cbAVP_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cbAVP.SelectedIndex >= 0)
            {
                avpID = Convert.ToInt32(cbAVP.SelectedValue);
            }
            else
                avpID = 0;
        }

        private void cbCulture_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cbCulture.SelectedIndex >= 0)
            {
                cultID = Convert.ToInt32(cbCulture.SelectedValue);
            }
            else
                cultID = 0;
        }

        private void cbYear_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cbYear.SelectedIndex >= 0)
            {
                year = Convert.ToInt32(cbYear.Items[cbYear.SelectedIndex].ToString());
            }
            else
                year = DateTime.Now.Year;
        }

        private void chbRegion_CheckedChanged(object sender, EventArgs e)
        {
            cbRegion.Enabled = chbRegion.Checked;
        }

        private void chbAVP_CheckedChanged(object sender, EventArgs e)
        {
            if (cbAVP.DataSource != null)
                cbAVP.Enabled = chbAVP.Checked;
            else
                cbAVP.Enabled = chbAVP.Checked = false;

        }

        private void chbCulture_CheckedChanged(object sender, EventArgs e)
        {
            cbCulture.Enabled = chbCulture.Checked;
        }

        private void chbYear_CheckedChanged(object sender, EventArgs e)
        {
            cbYear.Enabled = chbYear.Checked;
        }

        private void buttonShow_Click(object sender, EventArgs e)
        {
            dgvAVPHarvest.DataSource = null;
            dgvAVPHarvest.Columns.Clear();

            SqlConnection con = new SqlConnection(DBClass.ConStr);
            SqlCommand com = new SqlCommand("AVPHarvestReport", con);
            com.CommandType = CommandType.StoredProcedure;
            com.Parameters.Add("@Lang", SqlDbType.Bit).Value = InformationClass.Lang;
            com.Parameters.Add("@RegionID", SqlDbType.Int).IsNullable = true;
            com.Parameters.Add("@AVPID", SqlDbType.Int).IsNullable = true;
            com.Parameters.Add("@CultureID", SqlDbType.Int).IsNullable = true;
            com.Parameters.Add("@Year", SqlDbType.Int).IsNullable = true;

            if (chbRegion.Checked && regID > 0)
                com.Parameters["@RegionID"].Value = regID;
            else
                com.Parameters["@RegionID"].Value = DBNull.Value;

            if (chbAVP.Checked && avpID > 0)
                com.Parameters["@AVPID"].Value = avpID;
            else
                com.Parameters["@AVPID"].Value = DBNull.Value;

            if (chbCulture.Checked && cultID > 0)
                com.Parameters["@CultureID"].Value = cultID;
            else
                com.Parameters["@CultureID"].Value = DBNull.Value;

            if (chbYear.Checked && year >= 1991)
                com.Parameters["@Year"].Value = year;
            else
                com.Parameters["@Year"].Value = DBNull.Value;

            System.Data.DataTable tab = new System.Data.DataTable("Rep");
            SqlDataAdapter adapter = new SqlDataAdapter(com);
            try
            {
                adapter.Fill(tab);
                dgvAVPHarvest.DataSource = tab;

                dgvAVPHarvest.Columns["AVPName"].HeaderText = InformationClass.Lang ? "АВП" : "СИУ";
                
                for (int i = 0; i < dgvAVPHarvest.RowCount; i++)
                {
                    dgvAVPHarvest.Rows[i].HeaderCell.Value = (i + 1).ToString();
                }
                
                AddColumns();
            }
            catch (Exception ex)
            {
                //MessageBox.Show(ex.Message);
                MessageBox.Show(msgClass.msgNoData, msgClass.msgAchtung);
                dgvAVPHarvest.DataSource = null;
                dgvAVPHarvest.Columns.Clear();
            }

        }

        List<int> percentColIndex;

        int firstInd = 0, countColPerc = 0;
        int[] cols;
        string[] cultureNames = InformationClass.Lang ? DBClass.Cultures.Select(se => se.Name).Distinct().ToArray() : DBClass.Cultures.Select(se => se.Name).Distinct().ToArray();

        private void AddColumns()
        {
            percentColIndex = new List<int>();


            if (dgvAVPHarvest.ColumnCount > 0)
            {
                firstInd = dgvAVPHarvest.ColumnCount;

                for (int i = 0; i < dgvAVPHarvest.ColumnCount; i++)
                {
                    for (int j = 0; j < cultureNames.Length; j++)
                    {
                        if (dgvAVPHarvest.Columns[i].Name == cultureNames[j] + "_Gathering")
                        {
                            string name = cultureNames[j] + "_Harvest";
                            string haed = InformationClass.Lang ? ". Урожайность (т/га)" : ". Ӽосилдорлик (т/га)";
                            dgvAVPHarvest.Columns.Add(name, cultureNames[j] + haed);
                            dgvAVPHarvest.Columns[name].DisplayIndex = dgvAVPHarvest.Columns[i].DisplayIndex + 1;
                            percentColIndex.Add(dgvAVPHarvest.Columns[name].DisplayIndex);
                            countColPerc++;
                            break;
                        }
                    }
                }

                for (int i = 0; i < dgvAVPHarvest.ColumnCount; i++)
                {
                    if (dgvAVPHarvest.Columns[i].HeaderText.Contains("_Gathering"))
                    {
                        string gath = InformationClass.Lang ? ". Валовый сбор (т)" : ". Ялпи ҳосил (т)";
                        dgvAVPHarvest.Columns[i].HeaderText = dgvAVPHarvest.Columns[i].HeaderText.Replace("_Gathering", gath);
                    }
                    if (dgvAVPHarvest.Columns[i].HeaderText.Contains("_Area"))
                    {
                        string ar = InformationClass.Lang ? ". Посевнай площадь (га)" : ". Экин майдони (га)";
                        dgvAVPHarvest.Columns[i].HeaderText = dgvAVPHarvest.Columns[i].HeaderText.Replace("_Area", ar);
                    }
                }

                cols = new int[percentColIndex.Count];

                if (dgvAVPHarvest.RowCount > 0)
                    AddPercent();
            }
        }

        private void AddPercent()
        {
            for (int row = 0; row < dgvAVPHarvest.RowCount; row++)
            {
                for (int i = 0; i < percentColIndex.Count; i++)
                {
                    if (dgvAVPHarvest[firstInd - (percentColIndex.Count * 2) + i, row].Value != null && dgvAVPHarvest[firstInd - (percentColIndex.Count * 2) + i + 1, row].Value != null)
                        dgvAVPHarvest[firstInd + i, row].Value = ((decimal)dgvAVPHarvest[firstInd - (percentColIndex.Count * 2) + i * 2 + 1, row].Value / (decimal)dgvAVPHarvest[firstInd - (percentColIndex.Count * 2) + i * 2, row].Value).ToString("N2");
                    else
                        dgvAVPHarvest[firstInd + i, row].Value = "Error";
                }
            }
        }

        Sheets excelsheets;
        Worksheet excelworksheet;
        Microsoft.Office.Interop.Excel.Application excelapp;
        Workbook excelappworkbook;

        private void buttonExcel_Click(object sender, EventArgs e)
        {
            if (dgvAVPHarvest.RowCount > 0)
            {
                MessageBox.Show(msgClass.msgWaiting);
                GetReport();

                excelapp.Visible = true;
            }
        }

        private void GetReport()
        {
            excelapp = new Microsoft.Office.Interop.Excel.Application();
            excelapp.SheetsInNewWorkbook = 1;

            if (InformationClass.Lang)
                excelapp.Workbooks.Open(System.Windows.Forms.Application.StartupPath + @"\Templates\i11r13.xls", false, true, Type.Missing, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            else
                excelapp.Workbooks.Open(System.Windows.Forms.Application.StartupPath + @"\Templates\i11r13Uz.xls", false, true, Type.Missing, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

            excelappworkbook = excelapp.Workbooks[1];
            excelsheets = excelappworkbook.Worksheets;// .Worksheets[0]; //Sheets 

            int numPage = 1, left = 3, top = 9;

            excelworksheet = (Microsoft.Office.Interop.Excel.Worksheet)excelsheets.get_Item(numPage);

            excelworksheet.Cells.NumberFormat = "@";

            if (chbYear.Checked)
                excelworksheet.Cells[5, 3] = cbYear.Text;

            if (chbRegion.Checked)
                excelworksheet.Cells[4, 3] = cbRegion.Text;

            for (int i = 0; i < dgvAVPHarvest.Rows.Count; i++)
            {
                for (int j = 0; j < dgvAVPHarvest.Columns.Count; j++)
                {
                    excelworksheet.Cells[i + top, dgvAVPHarvest.Columns[j].DisplayIndex + left] = dgvAVPHarvest[j, i].FormattedValue;
                    addBorder(excelworksheet.Cells[i + top, dgvAVPHarvest.Columns[j].DisplayIndex + left]);
                }
                excelworksheet.Cells[i + top, 2] = i + 1;
                addBorder(excelworksheet.Cells[i + top, 2]);
            }

            int n = 0;
            for (int i = 0; i < dgvAVPHarvest.ColumnCount; i++)
            {

                for (int k = 0; k < cultureNames.Length; k++)
                {
                    if (dgvAVPHarvest.Columns[i].Name == cultureNames[k] + "_Area")
                    {
                        excelworksheet.get_Range(excelworksheet.Cells[7, i + 3 + n], excelworksheet.Cells[7, i + 5 + n]).Merge(Type.Missing);
                        excelworksheet.Cells[7, i + 3 + n] = cultureNames[k];
                        addBorder(excelworksheet.Cells[7, i + 3 + n]);
                        addBorder(excelworksheet.Cells[7, i + 4 + n]);
                        addBorder(excelworksheet.Cells[7, i + 5 + n]);

                        n++;
                    }
                }
            }

            for (int i = 0; i < n; i++)
            {
                excelworksheet.Cells[8, 4 + i * 3] = excelworksheet.Cells[8, 4];
                addBorder(excelworksheet.Cells[8, 4 + i * 3]);
                excelworksheet.Cells[8, 5 + (i * 3)] = excelworksheet.Cells[8, 5];
                addBorder(excelworksheet.Cells[8, 5 + (i * 3)]);
                excelworksheet.Cells[8, 6 + (i * 3)] = excelworksheet.Cells[8, 6];
                addBorder(excelworksheet.Cells[8, 6 + (i * 3)]);
            }

            excelworksheet.Columns.AutoFit();
            excelworksheet.Select(numPage);
        }

        private void addBorder(object p)
        {
            Range tmpRange = excelworksheet.get_Range(p, p);
            tmpRange.Borders[XlBordersIndex.xlEdgeLeft].LineStyle = XlLineStyle.xlContinuous;
            tmpRange.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous;
            tmpRange.Borders[XlBordersIndex.xlEdgeRight].LineStyle = XlLineStyle.xlContinuous;
            tmpRange.Borders[XlBordersIndex.xlEdgeTop].LineStyle = XlLineStyle.xlContinuous;
        }

        private void buttonPrint_Click(object sender, EventArgs e)
        {
            if (dgvAVPHarvest.RowCount > 0)
                PrintDGV.Print_DataGridView(dgvAVPHarvest);
        }
    }
}
